package com.catsic.zcjc.dao.jdbc;

import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.catsic.base.dao.impl.JdbcBaseDaoImpl;
import com.catsic.zcjc.enums.AreaType;
import com.catsic.zcjc.form.StationCountForm;

@Repository("jdbcStationDao")
public class JdbcStationDao extends JdbcBaseDaoImpl<StationCountForm, Long>{

	@Override
	public StationCountForm get(Long id) {
		return null;
	}

	@Override
	public void add(StationCountForm t) {
		
	}

	@Override
	public void delete(Long id) {
		
	}

	@Override
	public void update(StationCountForm t) {
		
	}

	@Override
	public Integer getTotalCount() {
		return null;
	}

	@Override
	public List<StationCountForm> getList(int startIndex, int count) {
		return null;
	}

	@Override
	public List<StationCountForm> getAll() {
		return null;
	}

	public List<Map<String,Object>> getByArea(String type,Integer areaId,String orgId) {
		if(StringUtils.isNotBlank(orgId)){
			orgId = " and s.OID in(select ID from S_ORGAN o where o.CODE like '"+orgId+"%')";
		}else{
			orgId = "";
		}
		
		String areaSql = "";
		if(areaId!=null&&areaId!=0){
			areaSql = " and p.id= '"+areaId+"'";
			if(AreaType.city.toString().equals(type)){
				areaSql = " and c.id= '"+areaId+"'";
			}else if(AreaType.district.toString().equals(type)){
				areaSql = " and d.id= '"+areaId+"'";
			}
		}
		String sql = "select p.id,p.name as areaName,p.latitude,p.longitude,count(*) as totalCount,sum(case when s.TYPE='1' then 1 else 0 end) as total1,sum(case when s.TYPE='2' then 1 else 0 end) as total2,sum(case when s.TYPE='3' then 1 else 0 end) as total3,sum(case when s.TYPE='4' then 1 else 0 end) as total4,sum(case when s.TYPE='5' then 1 else 0 end) as total5 from T_STATION s,t_district d,t_city c,t_province p where s.CITYCODE = d.id and d.city_id = c.id and c.province_id = p.id "+orgId+" "+areaSql+" group by p.id,p.name,p.latitude,p.longitude order by p.id";
		if(AreaType.city.toString().equals(type)){
			sql = "select c.id,c.name as areaName,c.latitude,c.longitude,count(*) as totalCount,sum(case when s.TYPE='1' then 1 else 0 end) as total1,sum(case when s.TYPE='2' then 1 else 0 end) as total2,sum(case when s.TYPE='3' then 1 else 0 end) as total3,sum(case when s.TYPE='4' then 1 else 0 end) as total4,sum(case when s.TYPE='5' then 1 else 0 end) as total5 from T_STATION s,t_district d,t_city c where s.CITYCODE = d.id and d.city_id = c.id "+orgId+" "+areaSql+"    group by c.id,c.name,c.latitude,c.longitude order by c.id";
		}else if(AreaType.district.toString().equals(type)){
			sql = "select d.id,d.name as areaName,d.latitude,d.longitude,count(*) as totalCount,sum(case when s.TYPE='1' then 1 else 0 end) as total1,sum(case when s.TYPE='2' then 1 else 0 end) as total2,sum(case when s.TYPE='3' then 1 else 0 end) as total3,sum(case when s.TYPE='4' then 1 else 0 end) as total4,sum(case when s.TYPE='5' then 1 else 0 end) as total5 from T_STATION s,t_district d where s.CITYCODE = d.id  "+orgId+" "+areaSql+"   group by d.id,d.name,d.latitude,d.longitude order by d.id";
		}
		
		if(areaId!=null&&areaId==0){
			sql = "select '' as id,'' as areaName,0 as latitude,0 as longitude,count(*) as totalCount,sum(case when s.TYPE='1' then 1 else 0 end) as total1,sum(case when s.TYPE='2' then 1 else 0 end) as total2,sum(case when s.TYPE='3' then 1 else 0 end) as total3,sum(case when s.TYPE='4' then 1 else 0 end) as total4,sum(case when s.TYPE='5' then 1 else 0 end) as total5 from T_STATION s where 1=1  "+orgId;
		}
		
		List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql);
		
		if(CollectionUtils.isEmpty(list)){
			if(areaId!=null&&areaId!=0){
				sql = "select p.id,p.name as areaName,p.latitude,p.longitude,0 as totalCount,0 as total1,0 as total2,0 as total3,0 as total4,0 as total5 from t_province p where p.id= "+areaId;
				if(AreaType.city.toString().equals(type)){
					sql = "select c.id,c.name as areaName,c.latitude,c.longitude,0 as totalCount,0 as total1,0 as total2,0 as total3,0 as total4,0 as total5 from t_city c where c.id= "+areaId;
				}else if(AreaType.district.toString().equals(type)){
					sql = "select d.id,d.name as areaName,d.latitude,d.longitude,0 as totalCount,0 as total1,0 as total2,0 as total3,0 as total4,0 as total5 from t_district d where d.id= "+areaId;
				}
				
				list = this.jdbcTemplate.queryForList(sql);
			}
		}
		
		return list;
	}
}
